import os
import uuid
import pytz
import openpyxl
import core.settings as settings

from abc import ABC, abstractmethod
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles import Border, Side, Alignment, Font, PatternFill
from openpyxl.cell import MergedCell
from typing import Union, Any
from dateutil.parser import parse
from datetime import datetime
from pathlib import Path

# 思路
# 1.生成文件名
# 2.生成文件数据
## 格式：[{}, {}...]
# pt = {
#     'id': None,     # 单元格坐标ID
#     'x': None,      # X轴位置
#     'y': None,      # Y轴位置
#     'value': '',    # 单元格的值
#     'bg_color': '',  # 单元格背景颜色
#     'is_edit': 1,   # 是否可以编辑，前端展示使用
#     'x_use': 1,     # X轴所占格数
#     'y_use': 1,     # Y轴所占格数
#     'is_heade': 1,     # 是否为表头
# }
# 3.保存
# 4.返回数据

tz: pytz.timezone = pytz.timezone(settings.TIME_ZONE)


class URLResult(object):
    def __init__(self, absolute, relative):
        self._ab_path = absolute
        self._re_path = relative

    @property
    def path(self):
        """
        绝对路径
        """
        return self._ab_path

    @property
    def url(self):
        """
        相对路径：用于前端下载
        """
        return "/" + self._re_path


class BaseXlsxWrite(ABC):
    def __init__(self, path=None):
        if path:
            self.r_xlsx = openpyxl.load_workbook(path)
        else:
            self.w_xlsx = openpyxl.Workbook()

        self.data = []
        self.hearde = []
        # 表头样式
        self.header_font = Font(bold=True, color="000000")
        self.header_fill = PatternFill("solid", fgColor="000000")
        self.header_alignment = Alignment(horizontal="center", vertical="center")
        # 设置边框
        self.thin_border = Border(
            left=Side(style="thin", color="000000"),
            right=Side(style="thin", color="000000"),
            top=Side(style="thin", color="000000"),
            bottom=Side(style="thin", color="000000"),
        )

    def _get_ws(self, ws=None):
        """
        从工作簿中检索或创建工作表
        """
        if not ws:
            ws = self.w_xlsx.worksheets[0]
        elif isinstance(ws, str):
            try:
                ws = self.w_xlsx[ws]
            except KeyError:
                ws = self.w_xlsx.create_sheet(ws)
        elif not isinstance(ws, Worksheet):
                raise TypeError('ws 必须是sheet对象或字符串：{0}'.format(ws.__class__.__name__))
        return ws

    def _check_format(self, list_keys: list | set, dict_list: list):
        keys_set = set(list_keys)
        if not all([keys_set.issubset(set(line.keys())) for line in dict_list]):
            raise ValueError("数据格式有误")

    @abstractmethod
    def create_xlsx_filename(self):
        """
        此方法必须由任何子类实现
        用于生成表示文件名（包括扩展名）的字符串
        报错：出发NotImplementedError：如果该方法未在子类中重写
        """
        raise NotImplementedError('必须实现创建文件名称的方法：create_xlsx_filename()')

    @abstractmethod
    def set_hearde(self):
        """
        此方法必须由任何子类实现
        用于生成表示文件名（包括扩展名）的字符串
        报错：出发NotImplementedError：如果该方法未在子类中重写
        """
        raise NotImplementedError('必须实现创建文件名称的方法：set_hearde()')

    @abstractmethod
    def get_data(self):
        """
        编写数据方法，生成最终数据为 self.data
        :return:
        """
        raise NotImplementedError('编写数据方法，生成最终数据为 self.data')

    def is_valid_filename(self, filename:str|None=None):
        """
        文件名不存在时生成文件名称
        存在时判断后缀是否符合
        :param filename: 文件名
        :return: str
        """
        if not filename:
            filename = self.create_xlsx_filename()
        if not any(filename.endswith(i) for i in ['xlsx', 'xls']):
            return f'{filename}.xlsx'
        return filename

    def write_data(self, ws=None):
        """
        处理格式：[{}, {}...]
        pt = {
            'id': None,     # 单元格坐标ID
            'x': None,      # X轴位置
            'y': None,      # Y轴位置
            'value': '',    # 单元格的值
            'bg_color': '',  # 单元格背景颜色
            'x_use': 1,     # X轴所占格数
            'y_use': 1,     # Y轴所占格数
        }
        """

        # 检查 data 格式
        list_keys = ['id', 'x', 'y', 'value', 'x_use', 'y_use']
        self._check_format(list_keys, self.data)

        # 获取 worksheet 对象
        ws = self._get_ws(ws)
        for line in self.data:
            if line['x_use'] != 1 or line['y_use'] != 1:
                # 合并单元格
                ws.merge_cells(
                    start_row=line['y'],
                    start_column=line['x'],
                    end_row=line['y'] + line['y_use'] - 1,
                    end_column=line['x'] + line['x_use'] - 1
                )
                # 设置合并单元格的外边框样式
                if line.get('is_heade'):
                    self.set_merged_borders(
                        ws=ws,
                        start_row=line['y'],
                        start_col=line['x'],
                        end_row=line['y'] + line['y_use'] - 1,
                        end_col=line['x'] + line['x_use'] - 1,
                        border_style=self.thin_border
                    )
            obj = ws.cell(row=line['y'], column=line['x'], value=line['value'])
            if line.get('is_heade'):
                obj.font = self.header_font
                obj.fill = self.header_fill
                obj.alignment = self.header_alignment
                obj.border = self.thin_border

    def save(self, filename):
        # 返回相对路径、绝对路径
        # 相对路径：用于前端下载，绝对路径：用于生成文件
        # 绝对路径
        ab_path = os.path.join(settings.EXCEL_ROOT, settings.EXCEL_URL, filename)
        re_path = os.path.join(settings.EXCEL_URL, filename)
        dir_path = os.path.join(settings.EXCEL_ROOT, settings.EXCEL_URL)
        if not os.path.exists(dir_path):
            os.makedirs(dir_path)
        self.w_xlsx.save(ab_path)
        ret_url = URLResult(absolute=ab_path, relative=re_path)
        return {'url': ret_url.url}

    def set_merged_borders(self, ws, start_row, start_col, end_row, end_col, border_style):
        """
        为合并单元格设置外边框（优化性能）

        参数:
            ws: 工作表对象
            start_row: 起始行
            start_col: 起始列
            end_row: 结束行
            end_col: 结束列
            border_style: Border对象
        """
        # 设置上边框
        for col in range(start_col, end_col + 1):
            cell = ws.cell(row=start_row, column=col)
            if not cell.border:
                cell.border = Border()
            cell.border = cell.border.copy(top=border_style.top)

        # 设置下边框
        for col in range(start_col, end_col + 1):
            cell = ws.cell(row=end_row, column=col)
            if not cell.border:
                cell.border = Border()
            cell.border = cell.border.copy(bottom=border_style.bottom)

        # 设置左边框
        for row in range(start_row, end_row + 1):
            cell = ws.cell(row=row, column=start_col)
            if not cell.border:
                cell.border = Border()
            cell.border = cell.border.copy(left=border_style.left)

        # 设置右边框
        for row in range(start_row, end_row + 1):
            cell = ws.cell(row=row, column=end_col)
            if not cell.border:
                cell.border = Border()
            cell.border = cell.border.copy(right=border_style.right)

    def run(self, filename=None):
        # 1.生成文件名称
        filename = self.is_valid_filename(filename)
        # 2.设置表头
        self.set_hearde()
        # 3.获取数据
        self.get_data()
        # 4.写入文件
        self.write_data()
        # 5.存储到指定位置
        return self.save(filename)


class BaseXlsxRead(ABC):
    def __init__(self, path):
        self.path = path
        self.wb = openpyxl.load_workbook(filename=path)
        self.data = None

    def parser_merged_cell(self, ws: Worksheet, row, col, merged_ranges):
        """
        判断单元格是否为合并单于格
        是：获取合并的偏移量，否：返回 None
        :param ws: 当前工作表对象
        :param row: 需要获取的单元格所在行
        :param col: 需要获取的单元格所在列
        :param merged_ranges: 所有合并单元格的范围
        :return:
        """
        cell = ws.cell(row=row, column=col)
        if not isinstance(cell, MergedCell):  # 判断该单元格是否为合并单元格
            for merged_range in merged_ranges:  # 循环查找该单元格所属的合并区域
                if cell.coordinate in merged_range:
                    # 获取合并区域左上角的单元格作为该单元格的值返回
                    coord = {
                        'x_use': merged_range.max_col - merged_range.min_col + 1,
                        'y_use': merged_range.max_row - merged_range.min_row + 1,
                    }
                    return cell, coord
            return cell, True
        return None, None

    def clean_data(self):
        ws = self.wb.worksheets[0]
        excel_data = []
        merged_ranges = ws.merged_cells.ranges  # 获取所有合并单元格的范围
        for row_index in range(1, ws.max_row + 1):
            for col_index in range(1, ws.max_column + 1):
                pt = {
                    'id': None,  # 单元格坐标ID
                    'x': None,  # X轴位置
                    'y': None,  # Y轴位置
                    'value': '',  # 单元格的值
                    'bg_color': '',  # 单元格背景颜色
                    'is_edit': 1,  # 是否可以编辑
                    'x_use': 1,  # X轴所占格数
                    'y_use': 1,  # Y轴所占格数
                }
                cell_, coord = self.parser_merged_cell(ws, row_index, col_index, merged_ranges)
                # 过滤合并单元格造成的空单元格
                if cell_ and coord:
                    pt['id'] = f'{col_index}_{row_index}'
                    pt['x'] = col_index
                    pt['y'] = row_index
                    cell_v = cell_.value

                    if cell_v:
                        pt['value'] = cell_v
                    # coord：合并单元格所占的 X、Y 轴的长度
                    if isinstance(coord, dict):
                        pt.update(coord)
                    excel_data.append(pt)
        self.data = excel_data

    @abstractmethod
    def save_data(self):
        raise NotImplementedError('必须实现数据入库的方法：save_data()')

    def run(self):
        # 清理数据
        self.clean_data()
        # 保存数据
        self.save_data()
